This forum is closed to new posts and
responses. Individual names altered for privacy purposes. The information contained in this website is provided for informational purposes only and should not be construed as a forum for customer support requests. Any customer support requests should be directed to the official HCL customer support channels below:
I've got some code that does't rely on a cursor, just gets a cell directly. The class below is a semi-complete automation class for handling open office, enjoy
Simon
Public Const OOO_HALIGN_NORMAL = 0
Public Const OOO_HALIGN_LEFT = 1
Public Const OOO_HALIGN_CENTER = 2
Public Const OOO_HALIGN_RIGHT = 3
%REM
public const XSHEETCONDITION_OPERATOR_NONE =
public const XSHEETCONDITION_OPERATOR_EQUAL =
public const XSHEETCONDITION_OPERATOR_NOT_EQUAL =
public const XSHEETCONDITION_OPERATOR_GREATER =
public const XSHEETCONDITION_OPERATOR_GREATER_EQUAL =
public const XSHEETCONDITION_OPERATOR_LESS =
public const XSHEETCONDITION_OPERATOR_LESS_EQUAL =
public const XSHEETCONDITION_OPERATOR_BETWEEN =
public const XSHEETCONDITION_OPERATOR_NOT_BETWEEN
public const XSHEETCONDITION_OPERATOR_FORMULA =
%END REM
Public Class OpenOfficeAutomation
Private m_oSM As Variant
Private m_oDesk As Variant
Private m_doc As Variant
Private m_filename As String
Private m_frame As String
Private m_urlFilename As String
Private m_currentSheet As String
'
'Converts a Ms Windows local pathname in URL (RFC 1738)
'
Private Function ConvertToUrl(strFile) As String
If Left( strFile, 2 ) = "\\" Then strFile = Mid$( strFile, 3 )
strFile = Replace(strFile, "\", "/")
strFile = Replace(strFile, ":", "|")
strFile = Replace(strFile, " ", "%20")
strFile = "file://" + strFile
ConvertToUrl = strFile
End Function
'
'A simple shortcut to create a service
'
Public Function CreateUnoService(Byval strServiceName As String) As Variant
Set CreateUnoService = m_oSM.createInstance(strServiceName)
End Function
'
' The filename to which we will ask OO to load & save
'
Property Get Filename As String
Filename = m_filename
End Property
Property Set Filename As String
m_filename = Filename
m_urlFilename = ConvertToURL( Filename )
End Property
Property Set Visible As Boolean
Dim oDocCtrl As Variant
Dim oDocFrame As Variant
Dim oDocWindow As Variant
Set oDocCtrl = m_Doc.getCurrentController()
Set oDocFrame = oDocCtrl.getFrame()
Set oDocWindow = oDocFrame.getContainerWindow()
oDocWindow.setVisible( Visible )
End Property
'
' A URL version of the filename
'
Private Property Get URLFilename As String
URLFilename = m_urlFilename
End Property
'
' The frame in to which documents are loaded and created
'
Property Get TargetFrame As String
TargetFrame = m_Frame
End Property
Property Set TargetFrame As String
m_Frame = TargetFrame
End Property
'
' THe current sheet that cell operations are performed on
'
Property Get CurrentSheet As String
CurrentSheet = m_currentSheet
End Property
Property Set CurrentSheet As String
m_currentSheet = CurrentSheet
End Property
'
' The constructor
'
Sub New
Set m_oSM = CreateObject("com.sun.star.ServiceManager")
Set m_oDesk = CreateUnoService("com.sun.star.frame.Desktop")
TargetFrame = "_blank"
End Sub
Public Function ObtainStructure( byName As String ) As Variant
Set obtainStructure = m_oSM.Bridge_GetStruct( byName )
End Function
Private Function MakePropertyValue(cName, uValue) As Variant
Dim oPropertyValue As Variant
Set oPropertyValue = m_oSM.Bridge_GetStruct("com.sun.star.beans.PropertyValue")
oPropertyValue.Name = cName
oPropertyValue.Value = uValue
Set MakePropertyValue = oPropertyValue
End Function
Sub OpenDocument( Byval ReadOnly As Boolean )
Dim OpenPar(0) As Variant
Set OpenPar(0) = MakePropertyValue("ReadOnly", ReadOnly)
Set m_doc = m_oDesk.loadComponentFromURL(URLFilename, TargetFrame, 0, OpenPar)
End Sub
Private Sub CreateOpenOfficeDocument( Byval docType As String )
Dim arg() As Variant
Set m_doc = m_oDesk.loadComponentFromURL( docType, TargetFrame, 0, arg() )
End Sub
Public Sub CreateCalc
CreateOpenOfficeDocument( "private:factory/scalc" )
CurrentSheet = "Sheet1"
End Sub
Public Sub CreateWriter
CreateOpenOfficeDocument( "private:factory/swriter" )
End Sub
Public Sub PrintDocument
m_doc.print
End Sub
Public Sub SaveDocumentAsPDF
Dim SaveParam(0) As Variant 'Parameters to save the doc
Public Function GetCell( Column As Integer, Row As Integer ) As Variant
Dim oSheet As Variant
Set oSheet = m_doc.Sheets.getByName(CurrentSheet)
Set GetCell=oSheet.getCellByPosition( Column, Row )
End Function
Public Sub CloseDocument
m_doc.Close( True )
End Sub
Public Function GetCellValue( Column As Integer, Row As Integer ) As Variant
Dim oCell As Variant
Set oCell = getCell( Column, Row )
GetCellValue = oCell.Value
End Function
Public Function GetCellFormula( Column As Integer, Row As Integer ) As String
Dim oCell As Variant
Set oCell = getCell( Column, Row )
GetCellFormula = oCell.Formula
End Function
Public Function GetCellString( Column As Integer, Row As Integer ) As String
Dim oCell As Variant
Set oCell = getCell( Column, Row )
Print oCell.String
GetCellString = oCell.String
End Function
Public Function SetCellValue( Column As Integer, Row As Integer, value As Variant ) As Variant
Set SetCellValue = getCell( Column, Row )
SetCellValue.setValue(value)
End Function
Public Function SetCellString( Column As Integer, Row As Integer, value As String ) As Variant
Set SetCellString = getCell( Column, Row )
SetCellString.setString(value)
End Function
Public Function SetCellFormula( Column As Integer, Row As Integer, value As String ) As Variant
Set SetCellFormula = getCell( Column, Row )
SetCellFormula.setFormula value
End Function
Public Sub SaveDocumentAs( szFilename As String )
filename = szFilename
SaveDocument
End Sub
Public Sub SaveDocument
Dim arg() As Variant
m_doc.storeAsURL URLFilename, arg()
End Sub
Public Function SelectRange( StartColumn As Integer, StartRow As Integer, EndColumn As Integer, EndRow As Integer ) As Variant
Dim oSheet As Variant
Set oSheet = m_doc.Sheets.getByName(CurrentSheet)
Set selectRange = oSheet.GetCellRangeByPosition( StartColumn, StartRow, EndColumn, EndRow )
End Function
Public Function ProtectDocument( Byval Password As String ) As Boolean
ProtectDocument = m_doc.Protect( password)
' ProtectDocument = m_doc.isProtected
End Function
Public Function ProtectSheet( Byval Password As String ) As Boolean
Dim oSheet As Variant
Set oSheet = m_doc.Sheets.getByName(CurrentSheet)
' m_doc.protect Password
ProtectSheet = oSheet.Protect( password)
' ProtectSheet = oSheet.isProtected
End Function
Public Sub ChangeColumnWidth( Index As Integer, WidthInMM As Long )
Dim oSheet As Variant
Dim oColumn As Variant
Set oColumn = getColumn( Index )
oColumn.Width = WidthInMM * 100
End Sub
Public Function GetColumn( Index As Integer ) As Variant
Dim oSheet As Variant
Set oSheet = m_doc.Sheets.getByName(CurrentSheet)
Set GetColumn = oSheet.columns.getByIndex( Index )
End Function
Public Function GetRow( Index As Integer ) As Variant
Dim oSheet As Variant
Set oSheet = m_doc.Sheets.getByName(CurrentSheet)
Set GetRow = oSheet.Rows.getByIndex( Index )
End Function
Public Function LongToSpreadSheet( Byval i As Long ) As String
Dim iResult As Long
LongToSpreadSheet = ""
While i >=1
iResult = i Mod 26
If iResult = 0 Then iResult = 26
LongToSpreadSheet = Mid$( ALPHABET, iResult, 1) & LongToSpreadSheet
i = ( i - iResult ) \ 26
Wend
End Function
' Given a sheet number, column number, and row number, returns the standard spreadsheet syntex, <Sheet String> : <Column String><Row number>
Public Function ConvertToString( Byval iColumn As Integer, Byval iRow As Integer ) As String
Dim szSheet As String
Dim szCol As String
Dim szRow As String
Dim iResult As Integer
If ( iColumn < 0 ) Or ( iRow < 0 ) Then
Stop
Else
ConvertToString = LongToSpreadSheet( iColumn + 1 ) & Cstr( iRow + 1 )
End If
End Function
End Class
Feedback response number WEBB8YNT9B created by ~Laura Nimhipigenoopsi on 10/01/2012
Re: OpenOffice Import
Return to top
. . Re: OpenOffice Import (~Laura Nimhipig... 1.Oct.12) 
